package cn.zjweu.dao.impl;

import cn.zjweu.dao.DishDao;
import cn.zjweu.domain.Dish;
import cn.zjweu.domain.DishCategory;
import cn.zjweu.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DishDaoImpl implements DishDao {
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;

    private void initThree() throws SQLException {
        conn = JDBCUtil.getConnection();
        pst = null;
        rs = null;
    }

    private void initTwo() throws SQLException {
        conn = JDBCUtil.getConnection();
        pst = null;
    }

    @Override
    public List<Dish> findCartListByDishIdAndUserId(int userId, int dishId) {
        String sql = "SELECT * FROM " +
                "(SELECT * FROM dish WHERE dishId =?) s, " +
                "userCart uc WHERE s.dishId = uc.dishId AND userId = ?";
        List<Dish> dishList = new ArrayList<>();
        try {
            initThree();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, dishId);
            pst.setInt(2, userId);
            System.out.println(pst);
            rs = pst.executeQuery();
            while (rs.next()) {
                Dish dish = new Dish();
                dish.setDishId(rs.getInt("dishId"));
                dish.setDishName(rs.getString("dishName"));
                dish.setDishPrice(rs.getBigDecimal("disPrice"));
                dish.setCount(rs.getInt("count"));
                dishList.add(dish);
            }
        } catch (SQLException e) {
            return null;
        } finally {
            JDBCUtil.close(rs, pst, conn);
        }
        return dishList;
    }

    @Override
    public DishCategory findCategoryNameById(Integer categoryId) {
        String sql = "SELECT * FROM dishCategory WHERE categoryId=?";
        DishCategory dishCategory = null;
        try {
            initThree();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, categoryId);
            System.out.println(pst);

            rs = pst.executeQuery();
            while (rs.next()) {
                dishCategory = new DishCategory();
                dishCategory.setCategoryId(categoryId);
                dishCategory.setCategoryName(rs.getString("categoryName"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pst, conn);
        }
        return dishCategory;
    }

    @Override
    public int findCountByDishAndCategory(String dishName, String categoryName) {
        StringBuilder sql = new StringBuilder("SELECT count(*) totalCount FROM dish WHERE 1=1");
        int i = 0;
        List<String> params = new ArrayList<>();
        if (dishName != null && dishName.length() > 0 && !"null".equals(dishName)) {
            sql.append(" AND dishName LIKE ?");
            params.add("%" + dishName + "%");
            i++;
        }
        if (categoryName != null && categoryName.length() > 0 && !"null".equals(categoryName)) {
            sql.append(" AND categoryId = \n" +
                    "(SELECT categoryId FROM dishCategory WHERE categoryName LIKE ?)");
            params.add("%" + categoryName + "%");
            i++;
        }
        try {
            initThree();
            pst = conn.prepareStatement(sql.toString());
            while (i > 0) {
                pst.setString(i, params.get(i - 1));
                i--;
            }
            System.out.println(pst.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                return rs.getInt("totalCount");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pst, conn);
        }
        return 0;
    }

    @Override
    public List<Dish> findPageByDishAndCategory(int start, int pageSize, String dishName, String categoryName) {
        StringBuilder sql = new StringBuilder("SELECT * FROM dish WHERE 1=1");
        int i = 0;
        List<Object> params = new ArrayList<>();
        if (dishName != null && dishName.length() > 0 && !"null".equals(dishName)) {
            sql.append(" AND dishName LIKE ?");
            params.add("%" + dishName + "%");
            i++;
        }
        if (categoryName != null && categoryName.length() > 0 && !"null".equals(categoryName)) {
            sql.append(" AND categoryId = \n" +
                    "(SELECT categoryId FROM dishCategory WHERE categoryName LIKE ?)");
            params.add("%" + categoryName + "%");
            i++;
        }
        sql.append(" LIMIT ?,?");
        params.add(start);
        params.add(pageSize);
        i += 2;

        try {
            initThree();
            pst = conn.prepareStatement(sql.toString());
            while (i > 0) {
                pst.setObject(i, params.get(i - 1));
                i--;
            }
            System.out.println(pst.toString());
            rs = pst.executeQuery();
            List<Dish> list = new ArrayList<>();
            while (rs.next()) {
                Dish dish = new Dish();
                dish.setDishId(rs.getInt("dishId"));
                dish.setDishName(rs.getString("dishName"));
                dish.setDishPrice(rs.getBigDecimal("disPrice"));
                dish.setCategoryId(rs.getInt("categoryId"));
                dish.setCooking(rs.getString("cooking"));
                dish.setMaterial(rs.getString("material"));
                dish.setDescription(rs.getString("description"));
                dish.setImagesRoute(rs.getString("imagesRoute"));
                list.add(dish);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pst, conn);
        }
        return null;
    }

    @Override
    public Dish findDishById(int dishId) {
        String sql = "SELECT * FROM dish WHERE dishId = ?";
        Dish dish = null;
        try {
            initThree();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, dishId);
            System.out.println(pst);
            rs = pst.executeQuery();
            while (rs.next()) {
                dish = new Dish();
                dish.setDishId(dishId);
                dish.setDishName(rs.getString("dishName"));
                dish.setDishPrice(rs.getBigDecimal("disPrice"));
                dish.setCategoryId(rs.getInt("categoryId"));
                dish.setCooking(rs.getString("cooking"));
                dish.setMaterial(rs.getString("material"));
                dish.setDescription(rs.getString("description"));
                dish.setImagesRoute(rs.getString("imagesRoute"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, pst, conn);
        }
        return dish;
    }
}
